package org.xpandaframework.base.curd.impl;

import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xpandaframework.base.exception.BaseDaoException;
import org.xpandaframework.base.utils.DateStyle;
import org.xpandaframework.base.utils.DateUtil;

/**
 * Description: 生成查询数量的SQL
 */
public class SQLGenerator<T> {

  protected Logger logger = LoggerFactory.getLogger(this.getClass());
  private final Set<String> columns;
  private final String tableName;
  private final String columnsStr;
  private final String pkName;

  // private final String seq;

  public SQLGenerator(Set<String> columns, String tableName, String pkName,
      String seq) {
    super();
    this.columns = columns;
    this.tableName = tableName;
    this.pkName = pkName;
    // this.seq = seq;
    this.columnsStr = StringUtils.join(this.columns, ",");
  }

  /**
   * 生成新增对象SQL
   *
   * @param t
   *          泛型
   * @param columnFieldNames
   *          数据库表->列
   * @return String SQL
   * @throws BaseDaoException
   */
  public String sqlInsert(T t, Map<String, String> columnFieldNames) throws BaseDaoException {
    final List<Object> values = getFieldValues(t, columnFieldNames);
    final StringBuilder sqlBuild = new StringBuilder();
    sqlBuild.append("INSERT INTO ");
    sqlBuild.append(this.tableName);
    sqlBuild.append("(");
    sqlBuild.append(this.columnsStr);
    sqlBuild.append(")values(");
    sqlBuild.append(StringUtils.join(values, ","));
    sqlBuild.append(")");
    final String sql = sqlBuild.toString();
    this.logger.debug("生成的新增SQL为: " + sql);
    return sql;
  }

  /**
   * 生成更新对象SQL
   *
   * @param t
   *          泛型
   * @param columnFieldNames
   *          数据库表->列
   * @return String SQL
   * @throws BaseDaoException
   */
  public String sqlUpdateById(T t, Map<String, String> currentColumnFieldNames) throws BaseDaoException {
    final List<String> values = getFieldValuesUpdate(t, currentColumnFieldNames);
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("UPDATE ");
    sql_build.append(this.tableName);
    sql_build.append(" SET ");
    sql_build.append(StringUtils.join(values, ","));
    sql_build.append(" WHERE ");
    sql_build.append("ID");
    sql_build.append(" = '");
    sql_build.append(getFieldIdValues(t));
    sql_build.append("'");
    final String sql = sql_build.toString();
    this.logger.debug("生成的更新SQL为: " + sql);
    return sql;
  }

  /**
   * 生成根据ID查询单条对象SQL
   *
   * @param id
   * @return String SQL
   * @throws BaseDaoException
   */
  public String sqlReadOneById(Object id) throws BaseDaoException {
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("SELECT ");
    sql_build.append(this.columnsStr);
    sql_build.append(" FROM ");
    sql_build.append(this.tableName);
    sql_build.append(" WHERE ID = '");
    sql_build.append(id);
    sql_build.append("'");
    final String sql = sql_build.toString();
    this.logger.debug("生成的查询SQL为: " + sql);
    return sql;

  }

  /**
   * 生成根据ID删除单挑数据SQL
   *
   * @param id
   * @return String SQL
   * @throws BaseDaoException
   */
  public String sqlDelById(Object id) throws BaseDaoException {
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("DELETE FROM ");
    sql_build.append(this.tableName);
    sql_build.append(" WHERE ");
    sql_build.append(this.pkName);
    sql_build.append(" = '");
    sql_build.append(id);
    sql_build.append("'");
    final String sql = sql_build.toString();
    this.logger.debug("生成的删除SQL为: " + sql);
    return sql;
  }

  /**
   * 根据columnFieldNames顺序，获取字段值
   *
   * @param t
   *          实体值
   * @param columnFieldNames
   *          数据库表->列
   * @return List<Object>
   */
  private List<Object> getFieldValues(T t, Map<String, String> columnFieldNames) {
    final List<Object> values = new LinkedList<Object>();
    for (final String column : this.columns) {
      Object value = ReflectionUtils.obtainFieldValue(t, columnFieldNames.get(column));
      values.add(handleValue(value));
    }
    return values;
  }

  /**
   * 根据值类型不同，处理字段值
   *
   * @param value
   *          要被处理的值
   * @return Object
   */
  private Object handleValue(Object value) {
    if (value instanceof String) {
      value = "\'" + value + "\'";
    } else if (value instanceof Date) {
      final Date date = (Date) value;
      final String dateStr = DateUtil.DateToString(date, DateStyle.YYYY_MM_DD_HH_MM_SS);
      // TODO 现在只是处理MYSQL数据库，将来增加oracle
      value = "TIMESTAMP('" + dateStr + "')";
    } else if (value instanceof Boolean) {
      final Boolean v = (Boolean) value;
      value = v ? 1 : 0;
    } else if ((null == value) || StringUtils.isBlank(value.toString())) {
      value = "''";
    }
    return value;
  }

  /**
   * 处理更新SQL字段的值
   *
   * @param t
   *          实体值
   * @param columnFieldNames
   *          数据库表->列
   * @return List<String>
   */
  private List<String> getFieldValuesUpdate(T t, Map<String, String> currentColumnFieldNames) {
    final List<String> colVals = new LinkedList<String>();
    for (final String column : this.columns) {
      final Object value = ReflectionUtils.obtainFieldValue(t, currentColumnFieldNames.get(column));
      if ((value != null) && !"ID".equals(column)) {
        colVals.add(column + "=" + handleValue(value));
      }
    }
    return colVals;
  }

  /**
   * 在泛型中，获取ID值
   *
   * @param t
   *          带值实体类
   * @return Object
   */
  private Object getFieldIdValues(T t) {
    return ReflectionUtils.obtainFieldValue(t, "id");
  }

  /************************************************************************************************************/

  /**
   * 生成新增的SQL
   * 
   * @param t
   * @param currentColumnFieldNames
   * @return
   */
  public String sql_create(T t, Map<String, String> currentColumnFieldNames) {
    final List<Object> values = getFieldValues(t, currentColumnFieldNames);

    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("INSERT INTO ").append(this.tableName).append("(")
        .append(this.columnsStr).append(")values(")
        .append(StringUtils.join(values, ",")).append(")");
    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;
  }

  /**
   * 生成批量新增的SQL
   * 
   * @param tList
   * @param currentColumnFieldNames
   * @return
   */
  public String sql_createOfBatch(List<T> tList,
      Map<String, String> currentColumnFieldNames, Object pkVal) {
    final StringBuilder sql_build = new StringBuilder();
    final int len = tList.size();
    int i = 0;

    // if (StringUtils.isNotEmpty(this.seq) && (null == pkVal)) {
    // ID使用序列
    // sql_build.append("INSERT INTO ").append(this.tableName).append("(")
    // .append(this.columnsStr).append(")");
    // sql_build.append(" SELECT ").append(this.seq).append(" , a.* FROM ( ");
    // for (; i < len; i++) {
    // final T t = tList.get(i);
    // final List<Object> values = getFieldValues(t,
    // currentColumnFieldNames);
    // values.remove(this.seq);
    //
    // if (i == 0) {
    // sql_build.append(" SELECT ");
    // } else {
    // sql_build.append(" UNION ALL SELECT ");
    // }
    // int j = 0;
    // final int vlen = values.size();
    // for (; j < vlen; j++) {
    // sql_build.append(values.get(j)).append(" AS T").append(j);
    // if (j != (vlen - 1)) {
    // sql_build.append(",");
    // }
    // }
    // sql_build.append(" FROM DUAL ");
    // }
    // sql_build.append(" ) a ");
    // } else {
    // ID没有使用序列
    sql_build.append("INSERT INTO ").append(this.tableName).append("(")
        .append(this.columnsStr).append(")");
    for (; i < len; i++) {
      final T t = tList.get(i);
      final List<Object> values = getFieldValues(t, currentColumnFieldNames);

      if (i == 0) {
        sql_build.append(" SELECT ");
      } else {
        sql_build.append(" UNION ALL SELECT ");
      }
      sql_build.append(StringUtils.join(values, ",")).append(
          " FROM DUAL ");
      // }
    }

    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;
  }

  /**
   * 生成根据ID删除的SQL
   * 
   * @param id
   * @return
   */
  public <PK> String sql_removeById(PK id) {
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("DELETE FROM ").append(this.tableName)
        .append(" WHERE ").append(this.pkName).append(" = ").append(id);

    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;
  }

  /**
   * 生成根据IDs批量删除的SQL
   * 
   * @param ids
   * @return
   */
  public <PK> String sql_removeOfBatch(List<PK> ids) {
    final StringBuilder sql_build = new StringBuilder();
    // sql_build.append("DELETE FROM ").append(this.tableName)
    // .append(" WHERE ").append(this.pkName).append(" IN ( 0 ");
    // int len = ids.size(), i = 0;
    // for (; i < len; i++) {
    // PK id = ids.get(i);
    // sql_build.append(",").append(id);
    // if ((i > 0) && ((i % (AssConstant.DELETE_CRITICAL_VAL - 1)) == 0)) {
    // sql_build.append(")").append(" OR ").append(this.pkName)
    // .append(" IN ( 0 ");
    // }
    // }
    // sql_build.append(")");

    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;
  }

  public String sql_removeAll() {
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("DELETE FROM ").append(this.tableName);
    final String sql = sql_build.toString();
    this.logger.debug("生成的SQL为: " + sql);
    return sql;
  }

  /**
   * 生成更新的SQL
   * 
   * @param t
   * @param currentColumnFieldNames
   * @return
   */
  public String sql_modify(T t, Map<String, String> currentColumnFieldNames) {
    final List<String> values = getFieldValuesUpdate(t, currentColumnFieldNames);
    Object id = ReflectionUtils.obtainFieldValue(t,
        currentColumnFieldNames.get(this.pkName));
    id = handleValue(id);

    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("UPDATE ").append(this.tableName).append(" SET ")
        .append(StringUtils.join(values, ",")).append(" WHERE ")
        .append(this.pkName).append(" = ").append(id);

    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;
  }

  /**
   * 生成根据ID查询的SQL
   * 
   * @param id
   * @return
   */
  public <PK> String sql_findOneById(PK id) {
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("SELECT ").append(this.columnsStr).append(" FROM ")
        .append(this.tableName)
        .append(" WHERE ROWNUM = 1 AND " + this.pkName + " = " + id);

    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;

  }

  /**
   * 生成查询所有的SQL
   * 
   * @return
   */
  public String sql_findAll() {
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("SELECT ").append(this.columnsStr).append(" FROM ")
        .append(this.tableName);
    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;
  }

  /**
   * 生成查询数量的SQL
   * 
   * @return
   */
  public String sql_findAllCount() {
    final StringBuilder sql_build = new StringBuilder();
    sql_build.append("SELECT COUNT(1) ").append(" FROM ")
        .append(this.tableName);
    final String sql = sql_build.toString();

    this.logger.debug("生成的SQL为: " + sql);

    return sql;
  }

}
